-- vim: set autoindent noexpandtab tabstop=4 shiftwidth=4 softtabstop=-1:
--[[
	Copyright (c) 2018,2019,2020 James Dornan

    This file is part of dbtable.

    dbtable is free software: you can redistribute it and/or modify
    it under the terms of the GNU General Public License as published by
    the Free Software Foundation, either version 3 of the License, or
    (at your option) any later version.

    dbtable is distributed in the hope that it will be useful,
    but WITHOUT ANY WARRANTY; without even the implied warranty of
    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
    GNU General Public License for more details.

    You should have received a copy of the GNU General Public License
    along with dbtable.  If not, see <https://www.gnu.org/licenses/>.
	
--]]

--- postgresql.lua
-- Allows access to a postgresql table as a Lua table. When used in the context
-- of a mintest mod the values are serialized, making tables a legal value,
-- and compressed if over  the 50 bytes in size. Not all methods are supported
-- by luajit at this time(3/2/2018), but are included in advance of support.
--
-- @module dbtable.postgresql
-- @author James Dornan <james@catch22.com>
-- @copyright James Dornan <james@catch22.com> 2018
-- @license GPLv3
-- @release 1.0

---@usage
local usage = [[
local tbl = require('dbtable').new({
    db         = 'postgresql',
    name       = 'test',
    connection = 'dbname=minetest host=localhost user=<username> password=<password>'
})

tbl[1] = {'Hello', 'World'}
tbl[2] = 'b'
tbl[3] = 'c'
tbl[5] = 'e'

print(tbl[1][1] .. ' ' .. tbl[1][2]) -- 'Hello World'

-- The above example would create a postgresql database, and then create within it
-- a table called 'test' with 2 columns, key and value. Key it set as a primary
-- key in the database, and must be unique.

-- This would be the result.

$psql minetest
minetest=# \d test
Table 'public.test'
Column |          Type          | Modifiers
-------+------------------------+-----------
key    | character varying(54)  | not null
value  | character varying(512) |
Indexes:
     'test_pkey' PRIMARY KEY, btree (key)

minetest=# select * from test;
key | value
----+-------
  1 | return {'Hello', 'World'}
  2 | b
  3 | c
  5 | e
 (4 row)

minetest=#
]]

--- table: dbtable.postgresql class
local postgresql = {}

--- Constructor function for dbtable.postgresql module
--
--  See: <a href=https://keplerproject.github.io/luasql/>luasql</a>,
--  <a href=https://forum.minetest.net/viewtopic.php?t=12471/>Trusted mods</a>,
--  <a href='http://lua-users.org/wiki/MetatableEvents/'>Metatable Events</a>
--  <a href='https://www.postgresql.org/docs/10/static/libpq-connect.html#LIBPQ-CONNSTRING'>Postgrsql connection string</a>
--
-- @table opts
-- @return Table object that has all the metamethods for acting as a Lua table [table]
function postgresql.new(opts)
	assert(type(opts.name)       == 'string')
	assert(type(opts.connection) == 'string')

	--- table: Used as self in the object
	local self        = {}
	--- table: Metatable to enable metamethods to impersonate a lua table
	local metatable   = {}
	--- string: Name of the postgresql table we will use
	local name        = opts.name
	--- string: Postgresql database connection string
	local conninfo    = opts.connection
	--- int: Maximum key size
	local keySize     = 64
	--- int: Maximum value size
	local valueSize   = 8192
	--- int: Minimum bytes before compression
	local minCompress = 50
	--- table: List of legal key types
	local legalKeys   = {
		string = true,
		number = true
	}
	--- table: List of legal value types
	local legalValues = {
		string  = true,
		number  = true,
		boolean = true
	}
	--- class: module luasql.postgresql
	local driver      = assert(
		require 'luasql.postgres',
		'Install luasql. https://keplerproject.github.io/luasql/'
	)
	--- class: postgresql driver
	local env         = assert(driver.postgres())
	--- resource: connection to postgresql database
	local con         = assert(env:connect(conninfo))
	--- resource: cursor used for return data
	local cur

	--- function: serialize
	local serialize
	--- function: deserialize
	local deserialize

	--- function: compress
	local compress
	--- function: decompress
	local decompress

	if opts.serialize and opts.deserialize then
		serialize   = opts.serialize
		deserialize = opts.deserialize
	end

	-- Add table as a legal value type
	if serialize then
		legalValues.table = true
	end

	if opts.compress and opts.decompress then
		compress   = opts.compress
		decompress = opts.decompress
	end

	if opts.keySize then
		keySize = opts.keySize
	end

	if opts.valueSize then
		valueSize = opts.valueSize
	end

	--- Set autocommit, or fail.
	assert(con:setautocommit(true))

	--- boonlean: True if the postgresql table was created using this instance
	self.created = false

	--- Returns true is the table exists and false if it does not.
	-- @return boolean
	function self:exists()
		local cur = assert(con:execute(string.format(
			[[
				SELECT 1
				FROM information_schema.tables
				WHERE table_schema = 'public' AND table_name = '%s'
			]],
			con:escape(name)
		)))

		if cur == nil then
			return false
		end

		local res = cur:fetch()
		cur:close()
		return res ~= nil
	end

	--- Drop the table from the database
	-- @return void
	function self:drop()
		local cur = assert(con:execute(string.format(
			[[DROP TABLE %s]],
			con:escape(name)
		)))
	end

	--- Locates the next lowest numberic key to use
	-- @return The next available numeric key [number]
	function self:nextKey()
	   local cur = assert(con:execute(string.format(
		   [[
			   SELECT  key::int + 1 AS nextkey
			   FROM	%s outside
			   WHERE   outside.key ~ '^\d+$' AND abs(outside.key::int) > 0
				   AND NOT EXISTS
				   (
					   SELECT NULL
					   FROM   %s inside
					   WHERE  (inside.key ~ '^\d+$' AND outside.key ~ '^\d+$')
					   AND
					   (abs(inside.key::int) > 0 AND inside.key::int = outside.key::int + 1)
				   )
			   ORDER BY key ASC LIMIT 1
		   ]],
		   con:escape(name),
		   con:escape(name)
		)))

		if cur == nil then
			return
		end

		local row = cur:fetch({}, 'a')

		cur:close()

		if row == nil then
			return 1
		end

		return row.nextkey
	end

	--- The number of rows in the postgresql table we're using
	-- @return number of rows [number]
	function self:len()
		local cur = assert(con:execute(string.format(
			[[SELECT count(1) AS count FROM %s]],
			con:escape(name)
		)))

		if cur == nil then
			return
		end

		local row = cur:fetch({}, 'a')

		cur:close()

		return row.count
	end

	--- Get a value from the table using a key
	-- @param key, either string or number
	-- @return string value from the table
	function self:get(key)
		assert(legalKeys[type(key)], 'Only number or string key.')

		local cur = assert(con:execute(string.format(
			[[SELECT value FROM %s WHERE key = '%s']],
			con:escape(name),
			con:escape(key)
		)))

		if cur == nil then
			return
		end

		local row = cur:fetch({}, 'a')

		cur:close()

		if row == nil then
			return
		end

		local value = row.value

		if decompress and string.sub(value, 1, 8) ~= 'return {' then
			 value = decompress(value)
		end

		if deserialize and string.sub(value, 1, 8) == 'return {' then
			 value = deserialize(value)
		end

		return value
	end

	--- Get the next row after this key in order. Useful for iteration.
	-- @param[opt] key string or number, before the desired row
	-- @return key
	-- @return value
	function self:next(key)
		assert(legalKeys[type(key)] or key == nil, 'Only number, string, or nil.')

		local cur
		local sql

		if key ~= nil then
			 sql = string.format(
				[[
					SELECT key, value
					FROM %s
					WHERE key > '%s'
					ORDER BY key
					LIMIT 1
				]],
				con:escape(name),
				con:escape(key)
			)
		else
			sql = string.format(
				[[SELECT key, value FROM %s ORDER BY key LIMIT 1]],
				con:escape(name)
			)
		end

		cur = assert(con:execute(sql))

		if cur == nil then
			return
		end

		local row = cur:fetch({}, 'a')

		cur:close()

		if row == nil then
			return
		end

		local value = row.value
		local key   = row.key

		if decompress and string.sub(value, 1, 8) ~= 'return {' then
			 value = decompress(value)
		end

		if deserialize and string.sub(value, 1, 8) == 'return {' then
			 value = deserialize(value)
		end

		return key, value
	end

	--- Get the smallest key value in the table
	-- @return the lowest key in the table
	function self:minKey()
		local cur = assert(con:execute(string.format(
			[[SELECT min(key) AS key FROM %s']],
			con:escape(name)
		)))

		if cur == nil then
			return
		end

		local row = cur:fetch({}, 'a')

		cur:close()

		if row ~= nil then
			return row.key
		end
	end

	--- Create table in postgresql based on the name, keysize and valuesize
	-- @return boolean based on success
	function self:create()
		if type(opts.keysize) == 'number' then
			keySize = opts.keysize
		end

		assert((keySize > 0 and keySize < 512), 'Key size out of range.')

		if type(opts.valuesize) == 'number' then
			valueSize = opts.valuesize
		end

		assert((valueSize > 0 and valueSize < 10240), 'Value size out of range.')

		local res = assert(con:execute(string.format(
			[[
				CREATE TABLE IF NOT EXISTS %s
				(
					key   VARCHAR(%u) PRIMARY KEY,
					value VARCHAR(%u)
				)
			]],
			con:escape(name),
			keySize,
			valueSize
		)))

		return type(res) == 'number'
	end

	--- Set new key value pair in the table
	-- @param key, string or number
	-- @param[opt] value If omitted the row is deleted
	-- @return value returned from the luasql execite fuction
	function self:set(key, value)
		assert(legalKeys[type(key)], 'Only number or string.')

		if value == nil then
			return self:del(key)
		end

		assert(legalValues[type(value)], 'Only number, string, or table.')

		if serialize and type(value) == 'table' then
			value = serialize(value)
		end

		if compress and string.len(value) > minCompress then
			 value = compress(value)
		end

		local res = assert(con:execute(string.format(
			[[
				INSERT INTO %s (key, value) VALUES ('%s', '%s')
				ON CONFLICT (key) DO UPDATE
				SET value = excluded.value
			]],
			con:escape(name),
			con:escape(key),
			con:escape(value)
		)))

		return res
	end

	--- Delete row from table
	-- @usage spawn.Tenplus1 = nil or spawn['Tenplus1'] = nil
	-- @param key The unique key to be deleted
	-- @return value returned from the luasql execite fuction
	function self:del(key)
		return assert(con:execute(string.format(
			[[ DELETE FROM  %s WHERE key = '%s' ]], 
			name,
			key
		)))
	end

	--- Create copy of table as a lua table
	-- @return table form of the data
	function self:export()
		local cur = assert(con:execute(string.format(
			[[SELECT key, value FROM %s ORDER BY key ASC]],
			name
		)))

		local data = {}
		local row = cur:fetch ({}, 'a')

		while row do
			data[row.key] = row.value
			row = cur:fetch ({}, 'a')
		end

		cur:close()

		return data
	end

	--- Import lua table
	-- @return void
	function self:import(data)
		for key, value in pairs(data) do
			self:set(key, value)
		end
	end

	--- Lookup row from postgresql table
	-- @usage print(spawn.Tenplus1.x) -- 2498
	-- @param key Key to look up in the table
	-- @return mixed, string , int, or table
	function metatable:__index(key)
		return self:get(key)
	end

	--- Add or up a row in the postgresql table
	-- @usage spawn.Tenplus1 = {15, 3, 1500} or spawn['Tenplus1'] = {15, 3, 1500}
	-- @param key The key to look up in the table
	-- @param value The new value to be added to the table
	-- @return boolean
	function metatable:__newindex(key, value)
		return self:set(key, value)
	end

	--- Append numerically
	-- @usage tbl(value) same as tbl[#tbl +] = value
	-- @param value Value to be added to the next numerical key in order
	function metatable:__call(value)
		return self:set(self:nextKey(), value)
	end

	--- Next key/value test from the given key
	-- @usage  key, value = next(sqltbl, key)
	-- @param key Key before the row we want to return
	-- @return key, value
	function metatable:__next(key)
		return self:next(key)
	end

	--- Used by the pairs function to loop through the table
	-- @usage for key, value in ipairs(tbl) do print(key .. ': ' .. value)) end
	-- @return function, table, nil
	function metatable:__pairs()
		local cur = assert(con:execute(string.format(
			[[SELECT key, value FROM %s ORDER BY key ASC]],
			name
		)))

		local pairsNext = function(table, key)
			if cur == nil then
				return
			end

			row = cur:fetch({}, 'a')

			if row ~= nil then
				return row.key, row.value
			end

			cur:close()
		end
		return pairsNext, self, nil
	end

	--- Used by the ipairs function to loop through the table
	-- @usage for key, value in ipairs(tbl) do print(key .. ': ' .. value)) end
	-- @return function, table, 0
	function metatable:__ipairs()
		local pairsNext = function(table, key)
			local value
			key = key + 1

			value = self:get(key)

			if value then
				return key, value
			end
		end

		return pairsNext, self, 0
	end

	--- For the # table length method
	-- @usage print(#tbl) -- May not work with luajit
	-- @return int Number of rows in the table
	function metatable:__len()
		return self:len()
	end

	--- String context
	-- @return string
	-- @todo Not sure what to do here at the moment
	function metatable:__tostring()
		return name
	end

	--- Garbage collection, gets called when there are no more references to the
	--  object.
	function metatable:__gc()
		if cur then
			pcall(cur:close())
		end

		pcall(con:close())
		pcall(env:close())
	end

	metatable.__metatable = {}

	setmetatable(self, metatable)

	if self:exists() then
		return self
	end

	assert(self:create(), 'Unable to create database table.')
	self.created = true

	return self
end

return postgresql
